﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using System.Text.RegularExpressions;
using System.Security.Cryptography;

namespace Migrator
{
    public class ConnectionInfo : IDisposable
    {
        public static readonly List<string> SUPPORTED_DBS = new List<string> { "mssql", "mysql" };
        public DbConnection Connection { get; private set; }
        public int CommandTimeout { get; private set; }
        public string Name { get; private set; }
        public string Type { get; private set; }

        private DbTransaction transaction;
        private DbProviderFactory factory;

        public static bool IsValidType(string type)
        {
            return type.HasValue() && SUPPORTED_DBS.Contains(type.ToLower());
        }

        public ConnectionInfo(string name, string type, string connectionString, int commandTimeout)
        {
            Name = name;
            Type = type;
            CommandTimeout = commandTimeout;

            string provider = "System.Data.SqlClient";

            if (Type == "mysql")
            {
                provider = "MySql.Data.MySqlClient";
            }

            factory = DbProviderFactories.GetFactory(provider);
            Connection = factory.CreateConnection();
            Connection.ConnectionString = connectionString;
        }

        private static readonly Regex SplitOnQuestionMarkRegex = new Regex(@"{\}", RegexOptions.Multiline);

        private DbCommand GetCommand() 
        {
            var cmd = Connection.CreateCommand();
            cmd.Transaction = transaction;
            return cmd;
        }

        public int Execute(string sql, params object[] parameters)
        {
            using (var cmd = GetCommand())
            {
                PrepareCommand(sql, parameters, cmd);

                return cmd.ExecuteNonQuery();
            }
        }

        private DbParameter CreateParameter(string name, object value)
        {
            DbParameter parameter = factory.CreateParameter();

            parameter.ParameterName = name;
            parameter.Value = value;

            return parameter;
        }

        private void PrepareCommand(string sql, object[] parameters, DbCommand cmd)
        {
            cmd.CommandTimeout = CommandTimeout;
            int i = 0;
            foreach (var param in parameters)
            {
                var name = "@param" + i;
                sql = sql.Replace("{" + i + "}", name);
                cmd.Parameters.Add(CreateParameter(name, param));
                i++;
            }
            cmd.CommandText = sql;
            return;
        }

        public bool Exists(string sql, params object[] parameters)
        {
            using (var cmd = GetCommand())
            {
                PrepareCommand(sql, parameters, cmd);
                using (var reader = cmd.ExecuteReader())
                {
                    return reader.Read();
                }
            }
        }

        public void Dispose()
        {
            if (Connection != null)
            {
                Connection.Dispose();
            }
        }

        internal void BeginTran()
        {
            transaction = Connection.BeginTransaction();
        }

        internal void CommitTran() 
        {
            transaction.Commit();
            transaction = null;
        }

        internal void RollbackTran()
        {
            transaction.Rollback();
            transaction = null;
        }
    }
    
}
